﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Drawing;
using System.Data.OleDb;

public partial class SGManager_Page_import : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    /// <summary>
    /// 保存
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button3_Click(object sender, EventArgs e)
    {
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            int proportionSum = 0;
           string str= GridView1.Rows[i].Cells[4].Text.Trim();
           int.TryParse(str, out proportionSum);
            string sql = "insert into [TB_House](ID,areaID,address,floor,proportionSum,rental,owner,ownerPhone,remark) values('" + GetGuid.Guid() +
                "','" + GridView1.Rows[i].Cells[1].Text.Trim() + "','" + GridView1.Rows[i].Cells[2].Text.Trim() + "','" +
                GridView1.Rows[i].Cells[3].Text.Trim() + "'," + proportionSum + ",'" +
                GridView1.Rows[i].Cells[5].Text.Trim() + "','" + GridView1.Rows[i].Cells[6].Text.Trim() + "','" +
                GridView1.Rows[i].Cells[7].Text.Trim() + "','" + GridView1.Rows[i].Cells[8].Text.Trim() + "')";
            DB.execnonsql(sql);
        }
        Response.Write("<script>alert('导入成功')</script>");
        this.GridView1.DataSource = null;
        this.GridView1.DataBind();
    }


    /// <summary>
    /// 导入按钮
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        FileUpload1.SaveAs(Server.MapPath("../ExcelFiles/") + "\\" + FileUpload1.FileName);

        string mystring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("../ExcelFiles/") + "\\" + FileUpload1.FileName + "';Extended properties=Excel 8.0";

        OleDbConnection conn = new OleDbConnection(mystring);
        conn.Open();
        OleDbDataAdapter myDa = new OleDbDataAdapter("select   *   from   [Sheet1$]", conn);
        DataSet myDs = new DataSet();
        myDa.Fill(myDs, "xls");
        DataTable dt_test = myDs.Tables[0];
        GridView1.DataSource = dt_test;
        GridView1.DataBind();
    }
    /// <summary>
    /// 导入数据到GridView
    /// </summary>
    /// <param name="fileName">源Excel文件</param>
    /// <param name="gridview">目标GridView</param>
    /// <param name="keyColIndex">导入主键列</param>
    /// <param name="rtnMsg">返回消息</param>
    /// <returns>返回没有导入的数据DataSet</returns>
    public static DataSet ExcelToGridView(string fileName, GridView gridview, int keyColIndex, string rtnMsg)
    {
        DataSet ds = null;
        string errMsg = string.Empty;
        try
        {
            if (!fileName.EndsWith(".xls"))
            {
                errMsg += "只能导入Excel格式的文件。";
            }

            ds = ExcelHelper.ExcelToDS(fileName);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < gridview.Rows.Count; i++)
            {
                if (gridview.Rows[i].RowType != DataControlRowType.DataRow)
                {
                    continue;
                }
                string key = gridview.Rows[i].Cells[keyColIndex].Text;
                if (String.IsNullOrEmpty(key))
                {
                    continue;
                }
                int k = 0;
                for (; k < dt.Rows.Count; k++)
                {
                    if (dt.Rows[k][gridview.Columns[keyColIndex].HeaderText].ToString().Replace(" ", "").Replace("　", "") == key)
                    {
                        break;
                    }
                }
                if (k == dt.Rows.Count)
                {
                    continue;
                }
                else
                {
                    for (int j = 0; j < gridview.Columns.Count; j++)
                    {
                        if (gridview.Columns[j].Visible == false)
                        {
                            continue;
                        }
                        if (gridview.Rows[i].Cells[j].Controls.Count == 0)
                        {
                            continue;
                        }
                        for (int m = 0; m < gridview.Rows[i].Cells[j].Controls.Count; m++)
                        {
                            if (gridview.Rows[i].Cells[j].Controls[m].Visible == false)
                            {
                                continue;
                            }
                            if (gridview.Rows[i].Cells[j].Controls[m] is TextBox)
                            {
                                if (!dt.Columns.Contains(gridview.Columns[j].HeaderText))
                                {
                                    continue;
                                }
                                TextBox txt = gridview.Rows[i].Cells[j].Controls[m] as TextBox;
                                string temp = dt.Rows[k][gridview.Columns[j].HeaderText].ToString();
                                try
                                {
                                    //           int intTemp = int.Parse(temp);
                                    //           txt.Text = intTemp.ToString();
                                    txt.Text = temp; //Translate(temp);
                                }
                                catch (Exception)
                                {
                                }
                                break;
                            }
                        }
                    }
                    dt.Rows[k].Delete();
                    dt.AcceptChanges();
                }
            }
        }
        catch (Exception ex)
        {
            errMsg += "导入失败." + ex.Message;
        }
        rtnMsg = errMsg;
        return ds;
    }

    /// <summary>
    /// 导入数据到GridView
    /// </summary>
    /// <param name="fileUpload">文件上传控件对象</param>
    /// <param name="filePath">要存放文件的服务器路径</param>
    /// <param name="gridview">目标GridView</param>
    /// <param name="keyColIndex">用于导入的主键列</param>
    /// <returns>返回没有导入的数据DataSet</returns>
    public static DataSet ExcelToGridView(FileUpload fileUpload, string filePath, GridView gridview, int keyColIndex, string strMsg)
    {
        string errMsg = string.Empty;
        DataSet ds = null;
        try
        {
            if (!filePath.EndsWith("\\"))
            {
                filePath += "\\";
            }
            string fileName = fileUpload.FileName.Replace(".xls", "") + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
            string fullFileName = filePath + fileName;
            fileUpload.SaveAs(fullFileName);
            ds = ExcelToGridView(fullFileName, gridview, keyColIndex, errMsg);
            if (errMsg != string.Empty)
            {
                errMsg += "将数据导入GridView失败." + errMsg;
            };
        }
        catch (Exception ex)
        {
            errMsg += "上传excel文件失败.";
        }
        strMsg = errMsg;
        return ds;
    }



    /// <summary>
    /// 选定主键列后，用这方法将重复行用黄色背景标志
    /// </summary>
    /// <param name="gridview">目标GridView</param>
    /// <param name="keyColIndex">导入主键列</param>
    public static void MarkRepetitionRows(GridView gridView, int keyColIndex)
    {
        for (int i = 0; i < gridView.Rows.Count; i++)
        {
            for (int j = i + 1; j < gridView.Rows.Count; j++)
            {
                if (gridView.Rows[i].Cells[keyColIndex].Text == gridView.Rows[j].Cells[keyColIndex].Text)
                {
                    gridView.Rows[i].BackColor = Color.Yellow;
                    gridView.Rows[j].BackColor = Color.Yellow;
                }
            }
        }
    }
}